﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DBUtility;
using System.Resources;
using System.Reflection;
using System.Configuration;


namespace BaseCoder.Utility
{
    public class DBHelper
    {
        private static string csql = @"SELECT
table_name=case when a.colorder=1 then d.name else '' end,
[table_desp]=case when a.colorder=1 then isnull(f.value,'') else '' end,
[col_id]=a.colorder,
[col_name]=a.name,
is_identity=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '1'else '0' end,
is_PrimaryKey=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
))) then '1' else '0' end,
col_type=b.name,
占用字节数=a.length,
col_lenght=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
is_Null=case when a.isnullable=1 then '1' else '0' end,
Col_default=isnull(e.text,''),
Col_desp=isnull(g.[value],'')
FROM syscolumns a
left join systypes b on a.xusertype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id
left join sys.extended_properties f on d.id=f.major_id and f.minor_id=0
where d.name='{TableName}'
order by a.id,a.colorder";
            //Properties.Resources.StringColumns; //Properties.Resources.Columns;

        /// <summary>
        /// 获得一个表的所有列
        /// </summary>
        /// <param name="connetionString">数据录链接字符串</param>
        /// <param name="tableName">表明</param>
        /// <returns></returns>
        public static TableColumn[] GetColumns(string connetionString, string tableName)
        {
            
            DataSet ds = SqlHelper.ExecuteDataset(connetionString, CommandType.Text, csql.Replace("{TableName}", tableName));
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                TableColumn[] columnes = new TableColumn[ds.Tables[0].Rows.Count];
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    columnes[i] = new TableColumn()
                    {
                        CanBeNull = (FieldConvert.ToInt(ds.Tables[0].Rows[i]["is_null"]) == 1),
                        ColName = FieldConvert.ToString(ds.Tables[0].Rows[i]["col_name"]),
                        DbType = FieldConvert.ToString(ds.Tables[0].Rows[i]["col_type"]),
                        Default = FieldConvert.ToString(ds.Tables[0].Rows[i]["col_Default"]),
                        IsDbGenerated = (FieldConvert.ToInt(ds.Tables[0].Rows[i]["is_identity"]) == 1),
                        IsPrimaryKey = (FieldConvert.ToInt(ds.Tables[0].Rows[i]["is_PrimaryKey"]) == 1),
                        Description = FieldConvert.ToString(ds.Tables[0].Rows[i]["col_desp"]),
                        ColLength = FieldConvert.ToInt(ds.Tables[0].Rows[i]["col_lenght"])
                    };
                }
                return columnes;
            }
            return new TableColumn[0];
        }//GetColumns

        /// <summary>
        /// 所得所有的表
        /// </summary>
        /// <param name="connetionString"></param>
        /// <returns></returns>
        public static Table[] GetTables(string connetionString)
        {
            string SpecificTable = new ProjectConfig(ConfigurationManager.AppSettings["Project"])["SpecificTable"];
            if (!string.IsNullOrEmpty(SpecificTable))
                SpecificTable = " And obj.name in (" + SpecificTable + ")";
            DataSet ds = SqlHelper.ExecuteDataset(connetionString, CommandType.Text, "Select obj.name,p.value from sysobjects obj left outer join  sys.extended_properties p on obj.id= p.major_id where obj.type = 'U' And (p.minor_id=0 or ISNULL(p.minor_id,0)=0)" + SpecificTable);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                Table[] tables = new Table[ds.Tables[0].Rows.Count];
                for (int i = 0; i < tables.Length; i++)
                {
                    tables[i] = new Table()
                    {
                        Name = Convert.ToString(ds.Tables[0].Rows[i]["Name"]),
                        Description = FieldConvert.ToString(ds.Tables[0].Rows[i]["Value"])
                    };
                }
                return tables;
            }
            return new Table[0];
        }//GetTables

        public static string GetCSharpDataType(string dbType)
        {
            switch (dbType.ToLower())
            {
                case "bit":
                    return "bool";
                case "varchar":
                case "char":
                case "ntext":
                case "text":
                case "nvarchar":
                case "nchar":
                case "xml":
                    return "string";
                case "int":
                case "bigint":
                case "smallint":
                    return "int";
                case "long":
                case "float":
                    return "double";
                case "decimal":
                case "smallmoney":
                case "money":
                case "numeric":
                    return "decimal";
                case "datetime":
                    return "DateTime";
                case "binary":
                case "varbinary":
                    return "byte[]";
                case "uniqueidentifier":
                    return "System.Guid";
                case "variant":
                default:
                    return "object";
            }
        }

        /// <summary>
        /// 获得格式化SQL数据库类型的方法
        /// </summary>
        /// <param name="dbType"></param>
        /// <returns></returns>
        public static string GetFormatDBTypeFn(string dbType)
        {
            switch (dbType.ToLower())
            {
                case "bit":
                    return "ToBool";

                case "varchar":
                case "char":
                case "ntext":
                case "text":
                case "nvarchar":
                case "nchar":
                case "xml":
                    return "ToString";
                case "int":
                case "bigint":
                case "smallint":
                    return "ToInt";
                case "long":
                case "float":
                    return "ToDouble";
                case "decimal":
                case "smallmoney":
                case "money":
                case "numeric":
                    return "ToDecimal";
                case "datetime":
                case "smalldatetime":
                    return "ToDateTime";
                case "binary":
                case "varbinary":
                    throw new Exception("数据库中binary类型数据格式的的方法还没有实现，请定义");
                case "uniqueidentifier":
                case "variant":
                default:
                    throw new Exception("未知的数据格式化方法");
            }

        }

        public static SqlDbType GetSqlDbType(string dbType)
        {
            switch (dbType.ToLower())
            {
                case "bit":
                    return SqlDbType.Bit;
                case "varchar":
                    return SqlDbType.VarChar;
                case "char":
                    return SqlDbType.Char;
                case "ntext":
                    return SqlDbType.NText;
                case "text":
                    return SqlDbType.Text;
                case "nvarchar":
                    return SqlDbType.NVarChar;
                case "nchar":
                    return SqlDbType.NChar;
                case "xml":
                    return SqlDbType.Xml;
                case "int":
                    return SqlDbType.Int;
                case "bigint":
                    return SqlDbType.BigInt;
                case "smallint":
                    return SqlDbType.SmallInt;
                case "float":
                    return SqlDbType.Float;
                case "decimal":
                    return SqlDbType.Decimal;
                case "smallmoney":
                    return SqlDbType.SmallMoney;
                case "money":
                    return SqlDbType.Money;
                case "numeric":
                    return SqlDbType.Int;
                case "datetime":
                case "smalldatetime":
                    return SqlDbType.DateTime;
                case "binary":
                    return SqlDbType.Binary;
                case "varbinary":
                    return SqlDbType.VarBinary;
                case "uniqueidentifier":
                    return SqlDbType.UniqueIdentifier;
                case "variant":
                    return SqlDbType.Variant;
                default:
                    throw new Exception("未知的数据格式化方法");
            }
        }
    }
}
